import datetime
from SystematicFactors.General import *
import os
import Core.Gadget as Gadget

# ---信用现象---
# 政府杠杆率
def Calc_Govt_Leverage(database, datetime1, datetime2):
    #
    df1 = EDB('M0001707', datetime1, datetime2)  # 其他存款性公司:对政府债权
    df2 = EDB('M0043411', datetime1, datetime2)  # 金融机构:财政存款余额
    df3 = pd.concat([df1, df2], axis=1)
    #
    df3['div'] = df3['M0001707'] / df3['M0043411']
    df3['log'] = np.log(df3['div'])
    df3['factor'] = df3['log'].diff()
    # df3['Report_Date'] = df3.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df3['Report_Date'] = df3.index
    Fill_ReleaseDate(df3, lag_release_month=1, release_day=14)
    print(df3[["Report_Date", "Release_Date", "factor"]])
    # return(df3,'Govt_Leverage_Dif')
    Save_Systematic_Factor_To_Database(database, df3, save_name='Govt_Leverage', field_name='div')
    Save_Systematic_Factor_To_Database(database, df3, save_name='Govt_Leverage_Monthly_Dif', field_name='factor')


# 流动性剩余
# M2-CPI-工业增加值
def Calc_Liquidity_Surplus(database, datetime1, datetime2):
    #
    df1 = Query_Data(database,'M0001385',datetime1,datetime2)  # M2:同比
    df2 = Query_Data(database,'M0000612',datetime1,datetime2)  # CPI:当月同比
    df3 = Query_Data(database,'M0000545',datetime1,datetime2)  # 工业增加值:当月同比
    df8 = pd.concat([df1,df2,df3],axis=1)
    df8['factor'] = df8['M0001385'] - df8['M0000612'] - df8['M0000545']
    # df8['Report_Date'] = df8.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df8['Report_Date'] = df8.index
    Fill_ReleaseDate(df8, lag_release_month=1, release_day=15)
    print(df8[["Report_Date", "Release_Date", "factor"]])
    Save_Systematic_Factor_To_Database(database, df8, save_name='Liquidity_Surplus', field_name='factor')
    return(df8,'Liquidity_Surplus')


def Calc_Bank_Surplus(database, datetime1, datetime2):

    df1 = Query_Data(database,'M0009969',datetime1,datetime2)  # 金融机构:各项贷款余额
    df2 = Query_Data(database,'M0009978',datetime1,datetime2)  # 金融机构:存贷差
    df3 = Query_Data(database,'M0000545',datetime1,datetime2)  # 工业增加值:当月同比
    df4 = Query_Data(database,'M0000273',datetime1,datetime2)  # 固定资产投资完成额:累计同比
    df9 = pd.concat([df1, df2, df3, df4], axis=1)
    df9['proportion'] = df9['M0009969'] / (df9['M0009969'] + df9['M0009978'])  # 贷款比例 = 贷款余额/（贷款余额+存贷差）
    df9['growth'] = (df9['M0000545'] + df9['M0000273']) / 100  # 实体增长率 = 工业+投资求和/100，原数据没有%
    df9['factor'] = 1 - df9['proportion'] - df9['growth']  # 1 - 贷款比例 - 实体增长率
    # df9['Report_Date'] = df9.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df9['Report_Date'] = df9.index
    Fill_ReleaseDate(df9, lag_release_month=1, release_day=15)
    print(df9[["Report_Date", "Release_Date", "factor"]])
    Save_Systematic_Factor_To_Database(database, df9, save_name='Bank_Surplus', field_name='factor')
    return(df9, 'Bank_Surplus')


# save_loan 银行存贷比
# 20240529迭代，Deposit_Loan_Ratio 原版中这个数值为 diff， Deposit_Loan_Ratio_Monthly_Diff 是二次差分
def Calc_Deposit_Loan_Ratio(database, datetime1, datetime2):
    #
    df1 = Query_Data(database,'M0009969',datetime1,datetime2)  # 金融机构:各项贷款余额
    df2 = Query_Data(database,'M0009978',datetime1,datetime2)  # 金融机构:存贷差
    df10 = pd.concat([df1,df2],axis=1)
    df10['div'] = df10['M0009969'] / (df10['M0009969'] + df10['M0009978'])
    df10['factor'] = df10['div'].diff()
    # df10['Report_Date'] = df10.index.astype(str).map(lambda x: datetime.datetime.strptime(x, '%Y-%m-%d'))
    df10['Report_Date'] = df10.index
    Fill_ReleaseDate(df10, lag_release_month=1, release_day=15)

    print(df10[["Report_Date", "Release_Date", "factor"]])
    Save_Systematic_Factor_To_Database(database, df10, save_name='Deposit_Loan_Ratio', field_name='div')
    Save_Systematic_Factor_To_Database(database, df10, save_name='Deposit_Loan_Ratio_Monthly_Diff', field_name='factor')
    return(df10,'Deposit_Loan_Ratio_Diff')



def Calc_Investable_Capital(database, datetime1, datetime2):

    df1 = Query_Data(database,'M0009978',datetime1,datetime2)  # 金融机构:存贷差
    df2 = Query_Data(database,'M0001693',datetime1,datetime2)  # 货币当局:储备货币:其他存款性公司存款
    df12 = pd.concat([df1,df2],axis=1)
    df12['sum'] = df12.sum(axis=1)
    df12['log'] = np.log(df12['sum'])
    df12['factor'] = df12['log'].diff()
    # df12['Report_Date'] = df12.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df12['Report_Date'] = df12.index
    Fill_ReleaseDate(df12, lag_release_month=1, release_day=15)
    print(df12[["Report_Date", "Release_Date", "sum", "factor"]])
    # df12.to_csv("d://investable_capital.csv")

    Save_Systematic_Factor_To_Database(database, df12, save_name='Invstable_Capital', field_name='sum')
    #
    df12.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df12, save_name='Invstable_Capital_Monthly_Dif', field_name='factor')
    return(df12, 'Invstable_Capital_Monthly_Dif')


def Calc_M0(database, datetime1, datetime2):
    #
    df29 = Query_Data(database,'M0001381',datetime1,datetime2)  # M0:同比
    # df29['Report_Date'] = df29.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df29['Report_Date'] = df29.index
    Fill_ReleaseDate(df29, lag_release_month=1, release_day=10)
    df29.columns=['factor','Report_Date','Release_Date']
    print(df29)
    #return(df29,'M0_YoY')
    Save_Systematic_Factor_To_Database(database, df29, save_name='M0_YoY', field_name='factor')


def Calc_M2_M1(database, datetime1, datetime2):

    df1 = Query_Data(database, 'M0001385',datetime1,datetime2)  # M2:同比
    df2 = Query_Data(database, 'M0001383',datetime1,datetime2)  # M1:同比
    df30 = pd.concat([df1, df2], axis=1)
    df30['factor']=df30['M0001385']-df30['M0001383']
    # df30['Report_Date'] = df30.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df30['Report_Date'] = df30.index
    Fill_ReleaseDate(df30, lag_release_month=1, release_day=10)
    print(df30)
    #return(df30,'M1_M2_Spread')
    Save_Systematic_Factor_To_Database(database, df30, save_name='M1_M2_Spread', field_name='factor')


def Calc_Money_Multiplier(database, datetime1, datetime2):

    df28 = Query_Data(database,'M0010131',datetime1,datetime2)  # 货币乘数
    df28['log'] = np.log(df28)
    df28['factor'] = df28['log'].diff()
    # df28['Report_Date'] = df28.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df28['Report_Date'] = df28.index
    Fill_ReleaseDate(df28, lag_release_month=1, release_day=13)
    print(df28)
    # return(df28,'Money_Multiplier_Dif')
    Save_Systematic_Factor_To_Database(database, df28, save_name='Money_Multiplier', field_name='M0010131')
    #
    df28.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df28, save_name='Money_Multiplier_Monthly_Dif', field_name='factor')


# newloan 新增人民币贷款
def Calc_Newloan(database, datetime1, datetime2):
    #
    df24 = Query_Data(database,'M0009973',datetime1,datetime2)  # 金融机构:新增人民币贷款:当月值
    df24['factor'] = df24.diff()
    # df24['Report_Date'] = df24.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df24['Report_Date'] = df24.index
    Fill_ReleaseDate(df24, lag_release_month=1, release_day=10)
    print(df24)
    # return(df24,'NewLoan_Dif')
    #
    Save_Systematic_Factor_To_Database(database, df24, save_name='NewLoan_Monthly', field_name='M0009973')
    df24.dropna(subset=["factor"], inplace=True)
    Save_Systematic_Factor_To_Database(database, df24, save_name='NewLoan_Monthly_Dif', field_name='factor')


def Calc_Newloan_Residuals(database, datetime1, datetime2):
    df1 = Query_Data(database,'M0009976',datetime1,datetime2)  # 金融机构:新增人民币贷款:居民户:当月值
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)  # 金融机构:新增人民币贷款:当月值
    df25 = pd.concat([df1,df2],axis=1)
    df25['factor'] = df25['M0009976'] / df25['M0009973']
    # df25['Report_Date'] = df25.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df25['Report_Date'] = df25.index
    Fill_ReleaseDate(df25, lag_release_month=1, release_day=10)
    print(df25)
    #return(df25,'NewLoan_Resid_Ratio')
    Save_Systematic_Factor_To_Database(database, df25, save_name='NewLoan_Resid_Ratio', field_name='factor')


def Calc_Newloan_Short_Term(database, datetime1, datetime2):
    df1 = Query_Data(database,'M0009974',datetime1,datetime2)  # 金融机构:新增人民币贷款:短期贷款及票据融资:当月值
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)  # 金融机构:新增人民币贷款:当月值
    df26 = pd.concat([df1,df2],axis=1)
    df26['factor'] = df26['M0009974'] / df26['M0009973']
    # df26['Report_Date'] = df26.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df26['Report_Date'] = df26.index
    Fill_ReleaseDate(df26, lag_release_month=1, release_day=10)
    print(df26)
    #return(df26,'NewLoan_ST_Ratio')
    Save_Systematic_Factor_To_Database(database, df26, save_name='NewLoan_ST_Ratio', field_name='factor')


def Calc_Newloan_Long_Term(database, datetime1, datetime2):
    df1 = Query_Data(database,'M0009975',datetime1,datetime2)  # 金融机构:新增人民币贷款:中长期:当月值
    df2 = Query_Data(database,'M0009973',datetime1,datetime2)  # 金融机构:新增人民币贷款:当月值
    df27 = pd.concat([df1,df2],axis=1)
    df27['factor'] = df27['M0009975'] / df27['M0009973']
    # df27['Report_Date'] = df27.index.astype(str).map(lambda x: pd.datetime.strptime(x, '%Y-%m-%d'))
    df27['Report_Date'] = df27.index
    Fill_ReleaseDate(df27, lag_release_month=1, release_day=10)
    print(df27)
    #return(df27,'NewLoan_LT_Ratio')
    Save_Systematic_Factor_To_Database(database, df27, save_name='NewLoan_LT_Ratio', field_name='factor')


def Calc_Social_Financing(database, datetime1, datetime2):
    # 社融数据
    df_cur_month = EDB('M5206730', datetime1, datetime2, 'Social_Financing_Cur_Month')  # 社会融资规模:当月值
    df_cur_month["Report_Date"] = df_cur_month.index
    Fill_ReleaseDate(df_cur_month, lag_release_month=1, release_day=15)  # 下个月11日更新， 保守选择15日发布
    Save_Systematic_Factor_To_Database(database, df_cur_month, save_name='Social_Financing_Cur_Month')

    # 为了计算同比，一定要比需求日期再提前一年
    datetime1_one_year = datetime1 - datetime.timedelta(days=400)
    df_balance = EDB('M5525755', datetime1_one_year, datetime2, 'Social_Financing_Balance')  # 社会融资规模:存量
    df_balance["Report_Date"] = df_balance.index
    Fill_ReleaseDate(df_balance, lag_release_month=1, release_day=15)

    # 社融存量同比增速
    df_balance["Social_Financing_Balance_YoY"] = df_balance["Social_Financing_Balance"] / df_balance["Social_Financing_Balance"].shift(12) * 100 - 100

    #
    Save_Systematic_Factor_To_Database(database, df_balance, save_name='Social_Financing_Balance')
    Save_Systematic_Factor_To_Database(database, df_balance, save_name='Social_Financing_Balance_YoY')


if __name__ == '__main__':
    #
    path_filename = os.getcwd() + "\..\Config\config_local.json"
    database = Config.create_database(database_type="MySQL", config_file=path_filename, config_field="MySQL")

    w.start()

    datetime1 = datetime.datetime(2023, 11, 1)
    datetime2 = datetime.datetime(2024, 5, 1)
    Calc_Deposit_Loan_Ratio(database, datetime1, datetime2)